﻿using Aspose.Cells;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.Xml.Linq;

namespace ExcelImport.Entity
{
    public enum ImportType : byte
    {
        Insert = 0,
        Update = 1
    }
    public class ExcelImportHelp
    {
        
      

        /// <summary>
        /// 得到外键表值
        /// </summary>
        /// <param name="codeTable">外键表</param>
        /// <param name="value">excel单元格中的值</param>
        /// <returns></returns>
        public static string GetCodeTableValue(XElement codeTable, string curretValue)
        {
            string CodeTableValue = null;
            
            using (var db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString))
            {
                var data = db.GetDataTable($@"select {(string)codeTable.Attribute("PrimaryKey")} 
                from {(string)codeTable.Attribute("Name")} where {(string)codeTable.Attribute("ReferenceColumn")}='{curretValue}'
                ");
                if (data.Rows.Count > 0)
                {
                    CodeTableValue = data.Rows[0][0].ObjToString();
                }
                else
                {
                    CodeTableValue = null;
                }
            }
            return CodeTableValue;
        }



        /// <summary>
        /// 得字段对应的最终值
        /// </summary>
        /// <param name="haveValue"></param>
        /// <param name="col"></param>
        /// <param name="curretValue"></param>
        /// <returns></returns>
        public static MsgModel GetSqlValueMsg(XElement col, string curretValue)
        {
            string defaultValue = null;
            if (curretValue != "")//取代码表值
            {
                return GetCodeTableValueMsgModel(col, curretValue);
            }
            else//无值用默认值
            {
                //用默认值
                defaultValue = GetDefaultValue((string)col.Attribute("DefaultValue"));
                if (defaultValue != "")//有值
                {
                    return GetCodeTableValueMsgModel(col, defaultValue);
                }//默认值为空，sqlValue为空""
                else
                {
                    return new MsgModel()
                    {
                        Status = true,
                        Msg = $"值取空字符串",
                        Value = ""
                    };
                }
            }

        }





        /// <summary>
        /// 根据数据行和xml 列配置得到当前列的值
        /// </summary>
        /// <param name="dataRow"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public static MsgModel GetColFinallyValue(Row dataRow, XElement col)
        {


            MsgModel value = null;
            string text = (string)col.Attribute("HeaderText");//列中文名称
            Cell currentCell = ((List<Cell>)HttpContext.Current.Items["TableHeader"]).FirstOrDefault(m => m.DisplayStringValue == text);//配置中的列名，在表头中找
            if (currentCell != null)
            {

                string excelCellValue = dataRow.GetCellOrNull(currentCell.Column)?.StringValue;
                //空值时赋""
                excelCellValue = excelCellValue ?? "";
                if (excelCellValue == "")//单元格值为空
                {
                    value = GetSqlValueMsg(col, "");
                }
                else//单元格有值
                {
                    value = GetSqlValueMsg(col, excelCellValue);
                    value.Msg = "第" + dataRow.Index + "行导入失败，失败原因为：字段" + value.Msg;
                }
            }
            else//找不到当前配置列在excel中
            {
                value = GetSqlValueMsg(col, "");
            }
            return value;
        }

        /// <summary>
        /// 得到代码表值信息实体
        /// </summary>
        /// <param name="col"></param>
        /// <param name="curretValue"></param>
        /// <returns></returns>
        public static MsgModel GetCodeTableValueMsgModel(XElement col, string curretValue)
        {
            string codeTableValue = null;
            ////判断当前列是否有清单表,有则取清单表中对应的值
            XElement CodeTable = col.Element("CodeTable");
            if (CodeTable != null)//说明有清单表，取清单表值
            {
                codeTableValue = GetCodeTableValue(CodeTable, curretValue);
                if (codeTableValue != null)
                {
                    return new MsgModel()
                    {
                        Status = true,
                        Msg = $"清单值获取成功",
                        Value = codeTableValue
                    };
                }
                else
                {
                    return new MsgModel()
                    {
                        Status = false,
                        Msg = $"《{(string)col.Attribute("HeaderText")}》的值有误,找不到对应清单值",
                        Value = null
                    };
                }
            }//有值，无清单表
            else
            {
                return new MsgModel()
                {
                    Status = true,
                    Msg = $"无清单表，取当前值",
                    Value = curretValue
                };
            }
        }

      

        /// <summary>
        /// 主键对应的值在表中存在则true,反之false (支持联合主键)
        /// </summary>
        /// <param name="columns"></param>
        /// <returns>true说明update，false说明为insert</returns>
        public static ImportType IsPrimaryKey(IEnumerable<XElement> columns, string tableName, Row dataRow)
        {
            //找出所有主键列
            var primaryKeyColumns = columns.Where(m => (string)m.Attribute("IsPrimaryKey") == "true");
            if (primaryKeyColumns.Count() > 0)
            {
                ////每个主键对应的值在表中都存在则update,反之insert
                List<string> whereCondition = new List<string>();
                foreach (var item in primaryKeyColumns)
                {
                    string text = (string)item.Attribute("HeaderText");//列中文名称
                    Cell currentCell = ((List<Cell>)HttpContext.Current.Items["TableHeader"]).FirstOrDefault(m => m.DisplayStringValue == text);
                    whereCondition.Add($@"{/*where条件拼接*/ ((string)item.Attribute("ColumnName"))  }
                                             ='{GetColFinallyValue(dataRow, item).Value}'
                                      ");
                }
                ////构造查询
                using (var db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString))
                {
                    string query = $"select count(1) from {tableName} where {string.Join(" and ", whereCondition)}";
                    int count = db.GetScalar(query).ObjToInt();
                    if (count > 0)//当前主键对应的值存在
                    {
                        return ImportType.Update;
                    }
                    else
                    {
                        return ImportType.Insert;
                    }
                }
            }
            else
            {
                return ImportType.Insert;
            }
        }


        public static string GetDefaultValue(string defaultValue)
        {
            string value = "";
            switch (defaultValue)
            {
                case "NewID":
                    value = Guid.NewGuid().ToString();
                    break;
                case "EmptyNewId":
                    value = Guid.Empty.ToString();
                    break;
                //case "Max":
                //    object obj = dbHelper.GetScalar($@"select max({columnName}) from {entityName}");
                //    revel = obj.IsValuable() ? ((obj.TryToInt() + 1).ToString()) : "1";
                //    break;
                //case "SerialNumber":
                //    revel = serialNumbers.Single(it => it.TableName == entityName).GetNumFuncWithDb(dbHelper);
                //    break;
                //case "CurrentUser":
                //    revel = PubGet.GetCurrentRegularMember.UserName.TryToString();
                //    break;
                case "CurrentDate":
                    value = DateTime.Now.ToString();
                    break;
                default:
                    if (!string.IsNullOrEmpty(defaultValue))
                    {
                        value = defaultValue;
                    }
                    break;
            }
            return value;
        }


        /// <summary>
        /// Skip为False时跳过，执形下一行
        /// </summary>
        /// <param name="table"></param>
        /// <param name="dataRow"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static VerificationModel Skip(XElement table, Row dataRow, IEnumerable<XElement> columns)
        {
            string tableName = (string)table.Attribute("Name");
            ImportType type = IsPrimaryKey(columns, tableName, dataRow);
            if (type == ImportType.Update)
            {
                //得到主键和Filter
                List<string> whereConditon = GetSkipConditon(table, dataRow, columns);
                using (var db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString))
                {
                    string query = $"select count(1) from {tableName} where {string.Join(" and ", whereConditon)}";
                    int count = db.GetScalar(query).ObjToInt();
                    if (count > 0)//当前主键对应的值存在
                    {
                        return new VerificationModel()
                        {
                            IsSuccess = true,
                            Msg = "跳过当前行的更新"
                        };
                    }
                    else
                    {//验证失败
                        return new VerificationModel()
                        {
                            IsSuccess = false,
                            Msg = "继续执行更新"
                        };
                    }
                }
            }
            else
            {
                //验证通过
                return new VerificationModel()
                {
                    IsSuccess = false,
                    Msg = "此时做Insert操作,继续执行更新"
                };
            }

        }
        public static List<string> GetSkipConditon(XElement table, Row dataRow, IEnumerable<XElement> columns)
        {
            //找出所有主键列
            var primaryKeyColumns = columns.Where(m => (string)m.Attribute("IsPrimaryKey") == "true");
            if (primaryKeyColumns.Count() > 0)
            {
                ////每个主键对应的值在表中都存在则update,反之insert
                List<string> whereCondition = new List<string>();
                foreach (var item in primaryKeyColumns)
                {
                    string text = (string)item.Attribute("HeaderText");//列中文名称
                    Cell currentCell = ((List<Cell>)HttpContext.Current.Items["TableHeader"]).FirstOrDefault(m => m.DisplayStringValue == text);
                    whereCondition.Add($@"{/*where条件拼接*/ ((string)item.Attribute("ColumnName"))  }
                                             ='{GetColFinallyValue(dataRow, item).Value}'
                                      ");
                }
                //获取 表上的Skip属性
                whereCondition.Add((string)table.Attribute("Skip"));
                return whereCondition;
            }
            else
            {
                return null;
            }
        }
        public static string importResult(List<string> errorLogList, int goodCount, string excellPath)
        {
            importResult.Value value = new importResult.Value()
            {
                total = errorLogList.Count,
                successTimes = goodCount,
                errorTimes = errorLogList.Count - goodCount,
                info = new List<string>(errorLogList),
                ErrorInfoExcelPath = excellPath
            };
            IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
            importResult.Rootobject jsonRoot = new importResult.Rootobject()
            {
                Key = 8,
                Value = value
            };
            return JsonConvert.SerializeObject(jsonRoot, Newtonsoft.Json.Formatting.Indented, timeFormat);
        }
    }
}